Jeudi 03 mai - Vendredi 04 mai
setwd(),.Rprofile avec des options qui modifie le comportement de R.options(stringsAsFactors = FALSE).rm(list = ls()) pour "rafraichir" votre session..Rdata en quittant/démarrant votre session R.Un ensemble d'outils basés sur une philosophie/grammaire commune.
if (!"tidyverse"%in%rownames(installed.packages())) {
install.packages("tidyverse")
}
library(tidyverse)
library(tidyverse) chargera les packages :
Ces packages représentent la base du tidyverse et sont en constante évolution.
tidyverse_update()
library(tidyverse)
tidyverse_conflicts()
tibble( x = 1:5, y = 1, z = x ^ 2 + y )
data.frame( x = 1:5, y = 1, z = x ^ 2 + y )
Et avec des noms de variables "exotiques" ?
data.frame(`1`= 1:3)
tibble( `;)` = 1:5, `42` = "1", `€` = `;)` ^ 2 + as.numeric(`42`) )
Les méthodes show() et print().
as.data.frame(mtcars)
print(as.data.frame(mtcars))
Les méthodes show() et print() .
as_tibble(mtcars)
print(as_tibble(mtcars))
La fonction rownames_to_column() (et column_to_rownames()).
as_tibble(rownames_to_column(mtcars))
print(as_tibble(rownames_to_column(mtcars)))
Changer les options d'affichages d'un tibble :
via options()
options(tibble.print_max = n, tibble.print_min = m, dplyr.print_min = p)
Afficher les n premières lignes, s'il y a plus de m lignes et sur p colonnes.
via print()
print(x = DF, n = n, width = p))
dans R Studio via View()
View(mtcars)
$, [ et [[mtcars$mpg
mtcars[["mpg"]]
mtcars[, "mpg"]
$, [ et [[as_tibble(mtcars)$mpg
as_tibble(mtcars)[["mpg"]]
as_tibble(mtcars)[, "mpg"]
De "vielles" fonctions limitées à la classe d'objet data.frame !
tb <- as_tibble(mtcars) class(tb)
class(as.data.frame(tb))
tibble, data.frame ou encore matrix ?Comment se comporte les opérations suivantes sur un data.frame et l'équivalent tibble ?
Quelle est la différence ?
df <- data.frame(abc = 1, xyz = "a")
df$xyz
df[["xyz"]]
df[, "xyz"]
df[, c("abc", "xyz")]Comment extraire une colonne en utilisant une variable ?
x <- "abc"
Comment savoir si un objet est de classe tibble, data.frame ou encore matrix ?
class(dta)
Comment se comporte les opérations suivantes sur un data.frame et l'équivalent tibble ?
Quelle est la différence ?
df1 <- data.frame(abc = 1, xyz = "a")
df2 <- as_tibble(df1)
df1$xyz
df1[["xyz"]]
df1[, "xyz"]
df1[, c("abc", "xyz")]Comment extraire une colonne en utilisant une variable ?
x <- "abc" df1[, x] df1[[x]]
x <- "abc" df2[, x] df2[[x]]
Manipuler des noms de variables non-conventionnelles :
dta <- tibble( `1` = 1:10, `2` = `1` * 2 + rnorm(length(`1`)) )
1.1 par rapport à 2.tibble avec une colonne nommée 3, contenant le résultat de 2 divisé par 1.tibble.Manipuler des noms de variables non-conventionnelles :
dta <- tibble( `1` = 1:10, `2` = `1` * 2 + rnorm(length(`1`)) )
Récupérez la variable nommée 1.
dta$`1`
Tracez un nuage de points de 1 par rapport à 2.
plot(dta$`1`, dta$`2`) ggplot(data = dta, aes(x = `1`, y = `2`)) + geom_point(colour = "white")
Créez un nouveau tibble avec une colonne nommée 3, contenant le résultat de 2 divisé par 1.
dta <- tibble( `1` = 1:10, `2` = `1` * 2 + rnorm(length(`1`)), `3` = `2`/ `1` )
Renommez les colonnes avec leurs noms littéral "un", "deux" et "trois", en construisant un nouveau tibble.
dta <- tibble( "un" = 1:10, "deux" = un * 2 + rnorm(length(un)), "trois" = deux / un )
vignette(package = "tibble")
library(pryr)
library(tidyverse)
tidyverse_conflicts()
L'opérateur "pipe" (%>%) provient du package magrittr développé par Stefan Milton Bache.
Par défaut library(tidyverse) s'occupe de charger l'opérateur "pipe".
Exemple :
x %>% f équivalent à f(x)x %>% f(y) équivalent à f(x, y)x %>% f %>% g %>% h équivalent à h(g(f(x)))f <- . %>% cos %>% sin équivalent à f <- function(.) sin(cos(.))Une aide à :
Note : Raccourci Ctrl+M dans R Studio pour faire apparaître un "pipe".
x <- rnorm(25) x1 <- sqrt(x) x2 <- na.exclude(x1) x3 <- x2<1 x4 <- table(x3) x5 <- prop.table(x4) x6 <- which.max(x5) x7 <- names(x6)
Les inconvénients de cette écriture :
ls()).Utilisation plus importante de la mémoire vive ?
mtcars2 <- mtcars %>% mutate(cyl_fac = factor(cyl)) pryr::object_size(mtcars) pryr::object_size(mtcars2) pryr::object_size(mtcars, mtcars2)
x <- rnorm(25) x <- sqrt(x) x <- na.exclude(x) x <- x<1 x <- table(x) x <- prop.table(x) x <- which.max(x) x <- names(x)
Les inconvénients de cette écriture :
x <- rnorm(25)
names(
which.max(
prop.table(
table(
na.exclude(
sqrt(x)
)<1
)
)
)
)
names(which.max(prop.table(table(na.exclude(sqrt(x))<1))))
Les inconvénients de cette écriture :
x %>% sqrt() %>% na.exclude() %>% `<`(1) %>% table() %>% prop.table() %>% which.max() %>% names()
A éviter si :
vignette(package = "magrittr")
library(readxl)
library(tidyverse)
tidyverse_conflicts()
Lire des tableaux rectangulaires :
read_csv() : fichier avec séparateur virgule (".csv").read_tsv() : fichier avec séparateur tabulation (\t).read_table() : fichier avec séparateur espace.read_delim() : forme générale (delim = "").Lire des données R (".rds") :
read_rds() : surcouche de readRDS() (sans la compression).Lire des formats plus exotiques :
read_fwf() : fichier à largeur fixe (nombre de caractères).read_log() : fichier de log provenant de serveur web Apache.locale()
"%Y-%m-%d".tibble.ratings <- read.csv( file = "./materials/ratings.csv" ) str(ratings[0, ])
ratings <- read_csv( file = "./materials/ratings.csv" )
read.csv(
text = "a,b,c
1,2,3
4,5,6"
)
read_csv( "a,b,c 1,2,3 4,5,6" )
L'argument skip.
read_csv("Une ligne de métadonnées
x,y,z
1,2,3", skip = 1)L'argument comment.
read_csv("# Une ligne de commentaires
x,y,z
1,2,3", comment = "#")L'argument col_names = TRUE/FALSE.
read_csv("1,2,3\n4,5,6", col_names = FALSE)Utiliser col_names pour nommer les colonnes.
read_csv("1,2,3\n4,5,6", col_names = c("a", "b", "c"))L'argument na.
read_csv("a,b,c\n1,2,3\n4,.,6", na = ".")L'argument col_types.
read_csv( "a,b,c\n1,x,2018-05-03\n4,y,2018-05-04", col_types = list(col_integer(), col_character(), col_date(format = "%Y-%m-%d")) )
Quelle fonction utiliseriez-vous pour importer un fichier avec | en séparateur ?
' ou double ".
read_csv() ?"x,y\n1,'a,b'"
Identifiez les problèmes des lignes suivantes.
read_csv("a,b\n1,2,3\n4,5,6")
read_csv("a,b,c\n1,2\n1,2,3,4")
read_csv("a,b\n\"1")
read_csv("a,b\n1,2\na,b")
read_csv("a;b\n1;3")Quelle fonction utiliseriez-vous pour importer un fichier avec | en séparateur ?
read_delim(file = path, delim = "|")
Un fichier CSV peut parfois contenir une virgule dans un champ.
Astuce : utilisez des guillemets simple ' ou double ".
read_csv() ? "quoteread_csv("x,y\n1,'a,b'", quote = "\'")
read_delim("x,y\n1,'a,b'", delim = ",", quote = "\'")Identifiez les problèmes des lignes suivantes.
read_csv("a,b\n1,2,3\n4,5,6")
read_csv("a,b,c\n1,2\n1,2,3,4")
read_csv("a,b\n\"1")
read_csv("a,b\n1,2\na,b")
read_csv("a;b\n1;3")readr inclut également des fonctions d'écriture write_*().
Exemple :
readr::write_csv(x = ratings, path = "ratings.csv")
writexl::write_xlsx(x = ratings, path = "ratings.xlsx")
library(tidyverse)
tidyverse_conflicts()
5 (+1) fonctions clés de dplyr :
filter()).arrange()).select()).mutate()).Résumer des valeurs (summarise()).
Travailler sur des sous-ensembles d'un jeu de données (group_by()).
Une syntaxe commune :
data.frame.data.frame (ou tibble).ratings <- read_csv(file = "./materials/ratings.csv")
filter(ratings, `Your Rating` >= 9, `IMDb Rating` >= 9)
ratings %>% filter(`Your Rating` >= 9 & `IMDb Rating` >= 9)
Trouvez le film avec le plus grand nombre de notes.
Trouvez les films contenant "shark" dans le titre (grepl()).
Trouvez les films vus entre le 25 avril 2018 et le 3 mai 2018.
Que fait la fonction between() ?
Trouvez le film avec le plus grand nombre de notes.
ratings %>% filter(`Num Votes`==max(`Num Votes`))
Trouvez le film avec le plus grand nombre de notes.
ratings %>% filter(`Num Votes`==max(`Num Votes`))
Trouvez les films contenant "shark" dans le titre (?grep).
ratings %>% filter(grepl(pattern = "shark", x = Title, ignore.case = TRUE))
Trouvez le film avec le plus grand nombre de notes.
ratings %>% filter(`Num Votes`==max(`Num Votes`))
Trouvez les films contenant "shark" dans le titre (?grep).
ratings %>% filter(grepl(pattern = "shark", x = Title, ignore.case = TRUE))
Trouvez les films vus entre le 25 avril 2018 et le 3 mai 2018.
ratings %>% filter(`Date Rated`>="2018-04-25" & `Date Rated`<="2018-05-03")
Trouvez le film avec le plus grand nombre de notes.
ratings %>% filter(`Num Votes`==max(`Num Votes`))
Trouvez les films contenant "shark" dans le titre (?grep).
ratings %>% filter(grepl(pattern = "shark", x = Title, ignore.case = TRUE))
Trouvez les films vus entre le 25 avril 2018 et le 3 mai 2018.
ratings %>% filter(`Date Rated`>="2018-04-25" & `Date Rated`<="2018-05-03")
Que fait la fonction between() ?
ratings %>%
filter(between(`Date Rated`, as.Date("2018-04-25"), as.Date("2018-05-03")))ratings %>% arrange(desc(`Date Rated`))
Réordonnez ratings selon les notes IMDb ("IMDb Rating") et utilisateur ("Your Rating").
Réordonnez ratings selon les titres des films ("Title"), par ordre alphabétique.
Réordonnez ratings selon les notes IMDb ("IMDb Rating") et utilisateur ("Your Rating").
ratings %>% arrange(desc(`IMDb Rating`), desc(`Your Rating`))
Réordonnez ratings selon les titres des films ("Title"), par ordre alphabétique.
ratings %>% arrange(Title)
Des aides à la sélection :
starts_with("abc") : noms commençant par "abc".ends_with("xyz") : noms finissant par "xyz".contains("ijk") : noms contenant "ijk".matches("(.)\\1") : noms correspondant à l'expression régulière.num_range("x", 1:3): noms correspondant à "x1", "x2" et "x3".ratings %>%
arrange(desc(`Date Rated`)) %>%
select(Title, ends_with("Rating"))
Sélectionnez les colonnes contenant "Date".
Sélectionnez les colonnes contenant "Title", "Your Rating", "Date Rated" et "Release Date" par leur indice.
Sélectionnez les colonnes contenant "Date".
ratings %>%
select(contains("Date"))Sélectionnez les colonnes contenant "Title", "Your Rating", "Date Rated" et "Release Date" par leur indice.
ratings %>% select(4, 2, 3, 12)
ratings %>% arrange(desc(`Date Rated`)) %>% mutate(Rating_is_better = `Your Rating`>=`IMDb Rating`) %>% select(Title, `Your Rating`, `IMDb Rating`, Rating_is_better)
Calculez la moyenne des notes "Your Rating" et "IMDb Rating".
Ajoutez les mois "Month" et jours "Day" à partir de "Date Rated" (?lubridate).
Calculez la moyenne des notes "Your Rating" et "IMDb Rating".
ratings %>% mutate(`Avg Rating` = (`Your Rating`+`IMDb Rating`)/2)
Ajoutez les mois "Month" et jours "Day" à partir de "Date Rated" (?lubridate).
ratings %>%
mutate(
Month = lubridate::month(`Date Rated`),
Day = lubridate::day(`Date Rated`)
)read_csv(file = "./materials/ratings.csv") %>%
summarise(
n_movies = n(),
my_average_rating = mean(`Your Rating`),
oldest_rating = min(`Date Rated`),
newest_rating = max(`Date Rated`)
)
Calculez la moyenne, l'écart-type, le minimum et le maximum des notes de "IMDb Rating".
Calculez le nombre de jours séparant la première note de la dernière.
Calculez la moyenne, l'écart-type, le minimum et le maximum des notes de "IMDb Rating".
ratings %>%
summarise(
mean = mean(`IMDb Rating`),
sd = sd(`IMDb Rating`),
min = min(`IMDb Rating`),
max = max(`IMDb Rating`)
)
ratings %>%
summarise_at(
.vars = vars(`IMDb Rating`),
.funs = funs(mean, sd, min, max)
)Calculez le nombre de jours séparant la première note de la dernière.
ratings %>%
summarise(
ndays = max(`Date Rated`)-min(`Date Rated`)
)group_by()read_csv(file = "./materials/ratings.csv") %>%
mutate(
Year = lubridate::year(`Date Rated`)
) %>%
filter(Year!="2014") %>%
group_by(Year) %>%
summarise(
n_movies = n(),
my_average_rating = mean(`Your Rating`),
users_average_rating = mean(`IMDb Rating`)
) %>%
arrange(desc(Year))
Calculez le temps total ("Runtime (mins)") et le nombre de films pour l'année 2017, par note ("Your Rating").
Repartez du fichier CSV et triez le résultat par ordre décroissant de la variable de temps total calculée.
read_csv(file = "./materials/ratings.csv") %>%
mutate(
Year = lubridate::year(`Date Rated`)
) %>%
filter(Year=="2017") %>%
group_by(`Your Rating`) %>%
summarise(
n_movies = n(),
runtime_all = sum(`Runtime (mins)`)
) %>%
arrange(desc(runtime_all))Cheatsheets R Studio : https://www.rstudio.com/resources/cheatsheets/
Site de dplyr : dplyr.tidyverse.org
vignette(package = "dplyr")
library(DBI)
library(odbc)
tidyverse_conflicts()
Lancer PostgreSQL via le raccourci "SQL Shell (psql)".
Laisser les valeurs par défaut.
Pour retirer le message d'avertissement, ajouter "chcp 1252" dans le fichier ".bat".
\? pour l'aide des commandes psql.\q quitter.\h aide des commandes sql.\l liste des bases de données.\c se connecter à une base.\d [nom] pour la description d'une table, d'un index, séquence, vue.\d liste des relations (tables, vues et séquences).\i nom_fichier.sql exécuter un fichier de commandes SQL.Créer un utilisateur.
CREATE ROLE test_user LOGIN password 'test_pwds';
Créer une base.
CREATE DATABASE test_data;
Modifier le propriétaire d'une base.
ALTER DATABASE test_data OWNER TO 'test_user';
Des connecteurs :
Identifier le pilote de connection.
odbcListDrivers() %>% filter(attribute=="Description") %>% select(-attribute)
Initier la connection.
con <- dbConnect( drv = odbc(), driver = "PostgreSQL ODBC Driver(UNICODE)", server = "localhost", port = 5432, database = "postgres", uid = "postgres", password = "password" )
con <- dbConnect(
drv = odbc(),
driver = "PostgreSQL ODBC Driver(UNICODE)",
server = "localhost",
port = 5432,
database = "postgres",
uid = "postgres",
password = rstudioapi::askForPassword("Mot de passe :")
)Créer un utilisateur.
dbSendQuery(con, SQL("CREATE ROLE test_user LOGIN password 'test_pwd';"))Créer une base.
dbSendQuery(con, SQL("CREATE DATABASE test_data;"))Modifier le propriétaire d'une base.
dbSendQuery(con, SQL("ALTER DATABASE test_data OWNER TO test_user;"))Se déconnecter de la base.
dbDisconnect(con)
Se connecter en tant que "test_user".
con <- dbConnect( drv = odbc(), driver = "PostgreSQL ODBC Driver(UNICODE)", server = "localhost", database = "test_data", port = 5432, uid = "test_user", password = "test_pwd" )
Lister les tables.
dbListTables(con)
Créer une table "mtcars".
mtcars %>%
rownames_to_column(var = "car") %>%
mutate(const = gsub(" .*", "", car)) %>%
select(const, everything()) %>%
dbWriteTable(con, "mtcars", .)
Lister les tables.
dbListTables(con)
Lister les champs d'une table.
dbListFields(con, "mtcars")
Lire une table.
dbReadTable(con, "mtcars") %>% as_tibble()
Extraire des données.
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE const='Merc'") dbFetch(res)
Libérer les ressources occupées par la requête.
dbClearResult(res) dbDisconnect(con)
En tant qu'utilisateur "data_user", exportez le fichier "ratings.csv" vers la base. Remplacez les espaces par des underscores dans les noms de colonnes.
Listez les noms de colonnes de la table créée.
Utilisez une requête SQL pour compter le nombre de films pour chaque note ("Your Rating").
Importez le résultat localement sous la forme d'un tibble.
Sélectionnez les films notés entre le 1er janvier 2017 et le 31 décembre 2017.
Exportez cette table vers la base de données.
con <- dbConnect(
drv = odbc(),
driver = "PostgreSQL ODBC Driver(UNICODE)",
server = "localhost",
database = "postgres",
port = 5432,
uid = "postgres",
password = "password"
)
dbSendQuery(con, SQL("CREATE ROLE data_user LOGIN password 'data_pwd';"))
dbSendQuery(con, SQL("CREATE DATABASE datawarehouse;"))
dbSendQuery(con, SQL("ALTER DATABASE datawarehouse OWNER TO data_user;"))
dbDisconnect(con)En tant qu'utilisateur "data_user", exportez le fichier "ratings.csv" vers la base. Enlevez les espaces dans les noms de colonnes.
ratings <- read_csv(file = "./materials/ratings.csv") %>%
`colnames<-`(gsub(" ", "", colnames(.)))
con <- dbConnect( drv = odbc(), driver = "PostgreSQL ODBC Driver(UNICODE)", server = "localhost", database = "datawarehouse", port = 5432, uid = "data_user", password = "data_pwd" ) dbWriteTable(con, "ratings", ratings)
Listez les noms de colonnes de la table créée.
dbListFields(con, 'ratings')
Utilisez une requête SQL pour compter le nombre de films pour chaque note ("YourRating").
Importez le résultat localement sous la forme d'un tibble.
dbSendQuery(
conn = con,
statement = SQL(
'SELECT "YourRating", COUNT(*) as count FROM ratings GROUP BY "YourRating";'
)
) %>%
dbFetch() %>%
as_tibble()Sélectionnez les films notés entre le 1er janvier 2017 et le 31 décembre 2017.
Exportez cette table vers la base de données.
dbSendQuery(
conn = con,
statement = SQL(
'SELECT *
FROM ratings
WHERE "DateRated">=\'2017-01-01\' AND "DateRated"<=\'2017-12-31\';'
)
) %>%
dbFetch() %>%
dbWriteTable(con, "ratings2017", .)
dbListTables(con)Liste de diffusion autour du package DBI: r-sig-db.
vignette(package = "DBI")
library(DBI)
library(odbc)
library(tidyverse)
library(dbplyr)
tidyverse_conflicts()
Connection sur une base de données SQL intégrée (package RSQLite).
con <- dbConnect(RSQLite::SQLite(), path = ":memory:")
Connection à la base PostgreSQL.
con <- dbConnect( drv = odbc(), driver = "PostgreSQL ODBC Driver(UNICODE)", server = "localhost", database = "datawarehouse", port = 5432, uid = "data_user", password = "data_pwd" )
Remplir la base.
ratings <- read_csv(file = "./materials/ratings.csv") %>%
`colnames<-`(gsub(" ", "", colnames(.))) %>%
mutate(Year = lubridate::year(DateRated))
copy_to(
dest = con,
df = ratings,
name = "ratings_idx",
temporary = FALSE,
indexes = list(
"DateRated",
"YourRating",
"Title",
"Year"
),
overwrite = TRUE
)Note : temporary = TRUE permet de rendre la table uniquement disponible à la connection en cours et uniquement pendant celle-ci.
Faire une référence à une table depuis une connection.
ratings_db <- tbl(con, "ratings") class(ratings_db)
Afficher l'objet.
ratings_db
Faire des requêtes sans SQL.
date_sevendaysago <- (Sys.Date()-7) ratings_db %>% filter(DateRated > date_sevendaysago) %>% select(Title, DateRated, YourRating, IMDbRating)
Faire des opérations en SQL sans SQL.
ratings_db %>%
group_by(Year) %>%
summarise(
N = n(),
AvgRating = mean(YourRating)
) %>%
arrange(Year)dplyr et tbl_df.
ratings_summary <- ratings %>%
group_by(Year) %>%
summarise(
N = n(),
AvgRating = mean(YourRating)
) %>%
arrange(Year)
str(ratings_summary, 1)
dplyr et tbl_sql.
ratings_summary_db <- ratings_db %>%
group_by(Year) %>%
summarise(
N = n(),
AvgRating = mean(YourRating)
) %>%
arrange(Year)
str(ratings_summary_db, 1)
L'envoi de la requête ne s'effectue qu'à la fin ou à l'affichage de l'objet.
ratings_summary_db %>% print()
Les données ne sont jamais téléchargées dans R (sauf mention explicite).
ratings_summary_db %>% collect()
Que fait dplyr pour effectuer la commande précédente ratings_summary_db ?
ratings_summary_db %>% show_query()
Traduire du R en requête SQL.
translate_sql(head(ratings_db))
Comment dbplyr traduit les fonctions as.numeric() et as.character() ?
Comment dbplyr traduit l'opérateur ^ ?
Comment dbplyr traduit les fonctions mean()et sd() ?
Et avec trim = 0.05 ou na.rm = TRUE ?
Comment dbplyr traduit les fonctions as.numeric() et as.character() ?
translate_sql(as.numeric(x))
translate_sql(as.character(x))
Comment dbplyr traduit l'opérateur ^ ?
translate_sql(x^2)
tbl(con, sql('select 1 as x')) %>%
mutate(sqr = x^2)Comment dbplyr traduit les fonctions mean()et sd() ?
Et avec trim = 0.05 ou na.rm = TRUE ?
translate_sql(mean(x))
translate_sql(mean(x, trim = 0.05))
translate_sql(mean(x, na.rm = TRUE))
collect() > tblRappatrier les données d'une requête via collect()
ratings_summary <- ratings_summary_db %>% collect() class(ratings_summary)
head() , tail() et nrow() sur ratings_summary_db.Utilisez les fonctions head() , tail() et nrow() sur ratings_summary_db.
Que se passe-t'il et pourquoi ?
ratings_summary_db %>% head(1)
ratings_summary_db %>% tail()
ratings_summary_db %>% nrow()
collect() > tblConnaître les dimensions de l'objet avant collect() ?
tally(ratings_summary_db)
explain() to meexplain(ratings_summary_db)
Connectez vous à la base "datawarehouse" en tant que "data_user".
Créez une table "mtcars" avec le jeu de données mtcars.
Comparez les fonctions dplyr::copy_to() (en utilisant l'argument temporary) et DBI::dbWriteTable().
Modifiez mtcars en passant les noms de lignes dans une colonne et en ajoutant une colonne constructeur "const".
Exporter l'objet mtcars vers la base.
Que se passe-t'il ? (argument overwrite).
Créez dans la base "datawarehouse" l'ensemble des tables de nycflights13.
Vérifiez que les tables sont bien créées.
Effectuez la jointure des tables planes et flights pour identifier les trois constructeurs ayant le plus grand nombre d'heures de vol (?dplyr::join).
Quelle est la requête SQL réalisée ?
Connectez vous à la base "datawarehouse" en tant que "data_user".
con <- dbConnect( drv = odbc(), driver = "PostgreSQL ODBC Driver(UNICODE)", server = "localhost", database = "datawarehouse", port = 5432, uid = "data_user", password = "data_pwd" )
Créez une table "mtcars" avec le jeu de données mtcars.
Comparez les fonctions dplyr::copy_to() (en utilisant l'argument temporary) et DBI::dbWriteTable().
copy_to(con, mtcars, "mtcars", temporary = TRUE, overwrite = TRUE) dbListTables(con)
copy_to(con, mtcars, "mtcars", temporary = FALSE, overwrite = TRUE) dbListTables(con)
dbWriteTable(con, "mtcars", mtcars, overwrite = TRUE) dbListTables(con)
Modifiez mtcars en passant les noms de lignes dans une colonne et en ajoutant une colonne constructeur "const".
Exporter l'objet mtcars vers la base.
Que se passe-t'il ? (argument overwrite).
mtcars %>%
rownames_to_column(var = "car") %>%
mutate(const = gsub(" .*", "", car)) %>%
select(const, everything()) %>%
dbWriteTable(con, "mtcars", ., overwrite = TRUE)Créez dans la base "datawarehouse" l'ensemble des tables de nycflights13.
Vérifiez que les tables sont bien créées.
library(nycflights13) dbWriteTable(con, "flights", flights) dbWriteTable(con, "airports", airports) dbWriteTable(con, "planes", planes) dbWriteTable(con, "weather", weather) dbWriteTable(con, "airlines", airlines) dbListTables(con)
Créez dans la base "datawarehouse" l'ensemble des tables de nycflights13.
Vérifiez que les tables sont bien créées.
library(nycflights13)
nycflights13_db <- ls("package:nycflights13") %>%
lapply(., function(df) {
copy_to(
df = get(df),
dest = con,
name = df,
overwrite = TRUE,
temporary = TRUE
)
}) %>%
`names<-`(ls("package:nycflights13"))
dbListTables(con)
Effectuez la jointure des tables planes et flights pour identifier les trois constructeurs ayant le plus grand nombre d'heures de vol (?dplyr::join).
longest_air_time_db <- nycflights13_db[["flights"]] %>% inner_join(nycflights13_db[["planes"]], by = "tailnum") %>% group_by(manufacturer) %>% summarise(total_air_time = sum(air_time)/60) %>% arrange(desc(total_air_time)) %>% select(manufacturer, total_air_time) %>% head(3) longest_air_time_db
Effectuez la jointure des tables planes et flights pour identifier les trois constructeurs ayant le plus grand nombre d'heures de vol (?dplyr::join).
Quelle est la requête SQL réalisée ?
longest_air_time_db %>% show_query()
Calculez la moyenne des températures "temp" (table weather) par station "origin" et par année "year".
Comparez le résultat de la version locale (tbl_df) et distante (tbl_sql).
Comptez le nombre d'aéroport dont le code "dest" (table flights) commence par la lettre "A" (Wildcard SQL; %like%).
Créez une nouvelle colonne "origin_dest" à partir de "origin" et "dest".
Calculez la moyenne des températures "temp" (table weather) par station "origin" et par année "year".
Comparez le résultat de la version locale (tbl_df) et distante (tbl_sql).
nycflights13::weather %>% group_by(origin, year) %>% summarise(avg_temp = mean(temp))
nycflights13_db[["weather"]] %>% group_by(origin, year) %>% summarise(avg_temp = mean(temp))
Calculez la moyenne des températures "temp" (table weather) par station "origin" et par année "year".
Comparez le résultat de la version locale (tbl_df) et distante (tbl_sql).
nycflights13::weather %>% group_by(origin, year) %>% summarise(avg_temp = mean(temp))
nycflights13_db[["weather"]] %>% group_by(origin, year) %>% summarise(avg_temp = mean(temp))
Comptez le nombre d'aéroport dont le code "dest" (table flights) commence par la lettre "A" (Wildcard SQL; %like%).
nycflights13_db[["flights"]] %>% filter(dest %like% 'A%') %>% summarise(count = n_distinct(dest))
Créez une nouvelle colonne "origin_dest" à partir de "origin" et "dest".
nycflights13_db[["flights"]] %>% mutate(origin_dest = origin %|| '-' ||% dest) %>% select(origin, dest, origin_dest)
nycflights13_db[["flights"]] %>% mutate(origin_dest = CONCAT(origin, "-", dest)) %>% select(origin, dest, origin_dest)
nycflights13_db[["flights"]] %>% mutate(origin_dest = paste(origin, dest, sep = "-")) %>% select(origin, dest, origin_dest)
Créez une nouvelle colonne "origin_dest" à partir de "origin" et "dest".
nycflights13_db[["flights"]] %>% mutate(origin_dest = origin %|| '-' ||% dest) %>% select(origin, dest, origin_dest)
nycflights13_db[["flights"]] %>% mutate(origin_dest = CONCAT(origin, "-", dest)) %>% select(origin, dest, origin_dest)
nycflights13_db[["flights"]] %>% mutate(origin_dest = paste(origin, dest, sep = "-")) %>% select(origin, dest, origin_dest)
Créez une nouvelle colonne "origin_dest" à partir de "origin" et "dest".
nycflights13_db[["flights"]] %>% mutate(origin_dest = origin %|| '-' ||% dest) %>% select(origin, dest, origin_dest)
nycflights13_db[["flights"]] %>% mutate(origin_dest = CONCAT(origin, "-", dest)) %>% select(origin, dest, origin_dest)
nycflights13_db[["flights"]] %>% mutate(origin_dest = paste(origin, dest, sep = "-")) %>% select(origin, dest, origin_dest)
Une base de données contenant des schémas décrivant des tables.
dbSendQuery(con, "CREATE SCHEMA nycflights13;")
dbWriteTable(con, SQL("nycflights13.flights"), flights)
La fonction tbl() cherche dans le schéma par défaut ("public" sur PostgreSQL).
tbl(con, "mtcars") %>% head()
dplyr::ìn_schema() permet de spécifier le schéma à utiliser.
tbl(con, in_schema("public", "mtcars")) %>% head()Accéder à une table présente dans un schéma autre que celui par défaut.
tbl(con, in_schema("nycflights13", "flights")) %>% head(1)
tbl(con, in_schema("nycflights13", "weather")) %>% head(1)Tutoriel SQL : www.codecademy.com
Le SQL en 10 étapes : blog.jooq.org
"Databases using R" : db.rstudio.com
library(xml2) library(rvest)
library(tidyverse)
tidyverse_conflicts()
Télécharger le code HTML et le convertir en XML.
read_html()
Extraire les noeuds.
html_nodes()
html_text()html_name()html_attrs()html_children()html_table()imdb_xml %>%
html_nodes("h1") %>%
html_text()Récupérer l'URL de la page casting du film.
rpo_cast_url <- "https://www.imdb.com/title/tt1677720/fullcredits/"
Récupérer la table contenant les acteurs et les personnages du film.
rpo_cast_list <- read_html(rpo_cast_url) %>%
html_nodes("table.cast_list")Récupérer la liste des personnages du film.
rpo_character_list <- rpo_cast_list %>%
html_nodes("td.character")Extraire le contenu des noeuds.
html_text(rpo_character_list) %>% head(2)
Formater le contenu.
rpo_character_list %>%
html_text() %>%
gsub("\n", "", .) %>%
gsub(" +", " ", .) %>%
gsub("^ (.*) $", "\\1", .) %>%
head(2)Reconstruction du tableau acteur/personnage.
cast_tbl <- tibble(
Actor = read_html(rpo_cast_url) %>%
html_nodes("td.itemprop") %>%
html_nodes("span.itemprop") %>%
html_text(),
Character = rpo_character_list %>%
html_text() %>%
gsub("\n", "", .) %>%
gsub(" +", " ", .) %>%
gsub("^ (.*) $", "\\1", .)
)
cast_tbl %>%
head(3)Lecture du fichier contenant les notes des films.
ratings <- read_csv("materials/ratings.csv") %>%
`colnames<-`(gsub(" ", "", colnames(.))) %>%
mutate(
Year = lubridate::year(DateRated),
Month = lubridate::month(DateRated),
Day = lubridate::wday(DateRated)
)Création d'un nouveau utilisateur et d'une nouvelle base.
con <- dbConnect(
drv = odbc(),
driver = "PostgreSQL ODBC Driver(UNICODE)",
server = "localhost",
database = "postgres",
port = 5432,
uid = "postgres",
password = "password"
)
dbSendQuery(con, SQL("CREATE ROLE imdb_user LOGIN password 'imdb_pwd';"))
dbSendQuery(con, SQL("CREATE DATABASE movies;"))
dbSendQuery(con, SQL("ALTER DATABASE movies OWNER TO imdb_user;"))
dbDisconnect(con)Connection avec "imdb_user" sur "movies".
con <- dbConnect( drv = odbc(), driver = "PostgreSQL ODBC Driver(UNICODE)", server = "localhost", database = "movies", port = 5432, uid = "imdb_user", password = "imdb_pwd" )
Création de la table ratings (et d'un objet tbl_sql).
ratings_db <- copy_to( dest = con, df = ratings, names = "ratings", overwrite = TRUE, temporary = FALSE )
Extraction du casting d'un film provenant de la table ratings.
cast_list <- ratings_db %>%
filter(Title %like% "Ready Player One") %>%
collect() %>%
.[["URL"]] %>%
paste0(., "fullcredits/") %>%
read_html() %>%
html_nodes("table.cast_list")Création d'un tableau casting.
cast_tbl <- tibble(
Actor = cast_list %>%
html_nodes("td.itemprop") %>%
html_nodes("span.itemprop") %>%
html_text(),
Character = cast_list %>%
html_nodes("td.character") %>%
html_text() %>%
gsub("\n", "", .) %>%
gsub(" +", " ", .) %>%
gsub("^ (.*) $", "\\1", .)
)Fontion de récupération du casting d'un film.
get_cast <- function(x) {
x %>%
paste0(., "fullcredits/") %>%
read_html() %>%
html_nodes("table.cast_list") %>%
(function(node) {
cast_tbl <- tibble(
URL = x,
Actor = node %>%
html_nodes("td.itemprop") %>%
html_nodes("span.itemprop") %>%
html_text(),
Character = node %>%
html_nodes("td.character") %>%
html_text() %>%
gsub("\n", "", .) %>%
gsub(" +", " ", .) %>%
gsub("^ (.*) $", "\\1", .)
)
}) %>%
return()
}Fontion de récupération du casting d'un film.
get_cast("https://www.imdb.com/title/tt0100240/")Ajout du casting dans ratings_db (local).
casting <- ratings_db %>%
select(URL) %>%
collect() %>%
head() %>%
mutate(
Casting = map(URL, get_cast)
)
castingEn utilisant le lien url : https://www.imdb.com/user/ur56341222/ratings.
Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note ("étoile jaune").
Compilez des statistiques (moyenne, écart-type, etc.) dans une nouvelle table.
Exportez ces tables vers une base de données.
Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note ("étoile jaune").
movies_list <- "https://www.imdb.com/user/ur56341222/ratings" %>%
read_html() %>%
html_nodes("div#ratings-container.lister-list") %>%
html_nodes("div.lister-item.mode-detail")
title <- movies_list %>%
html_nodes("h3.lister-item-header") %>%
html_text() %>%
gsub("\n", "", .) %>%
gsub(" [0-9]+.", "", .) %>%
gsub(" +", " ", .) %>%
gsub("^ (.*) $", "\\1", .)Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note ("étoile jaune").
movies_list <- "https://www.imdb.com/user/ur56341222/ratings" %>%
read_html() %>%
html_nodes("div#ratings-container.lister-list") %>%
html_nodes("div.lister-item.mode-detail")Genres.
genres <- movies_list %>%
html_nodes("span.genre") %>%
html_text() %>%
gsub("\n", "", .) %>%
gsub(" +", " ", .) %>%
gsub("^ (.*) $", "\\1", .)Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note ("étoile jaune").
movies_list <- "https://www.imdb.com/user/ur56341222/ratings" %>%
read_html() %>%
html_nodes("div#ratings-container.lister-list") %>%
html_nodes("div.lister-item.mode-detail")Durée du film.
runtime <- movies_list %>%
html_nodes("span.runtime") %>%
html_text() %>%
gsub("\n", "", .) %>%
gsub(" +", " ", .) %>%
gsub("^ (.*) $", "\\1", .)Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note ("étoile jaune").
movies_list <- "https://www.imdb.com/user/ur56341222/ratings" %>%
read_html() %>%
html_nodes("div#ratings-container.lister-list") %>%
html_nodes("div.lister-item.mode-detail")Note.
rating <- movies_list %>%
html_nodes("div.ipl-rating-widget") %>%
html_nodes("div.ipl-rating-star.small") %>%
html_text("span.ipl-rating-star__rating") %>%
unique() %>%
gsub("\n", "", .) %>%
gsub(" +", " ", .) %>%
gsub("^ (.*) $", "\\1", .) %>%
as.numeric() %>%
(function(.x) {if (length(.x)!=2) {c(NA, NA)} else {.x}})Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note ("étoile jaune").
get_movies_info <- function(x) {
c(
x %>% html_nodes("h3.lister-item-header") %>% html_text() %>% gsub("\n", "", .) %>%
gsub(" [0-9]+.", "", .) %>% gsub(" +", " ", .) %>% gsub("^ (.*) $", "\\1", .),
x %>% html_nodes("span.genre") %>% html_text() %>%
gsub("\n", "", .) %>% gsub(" +", " ", .) %>% gsub("^ (.*) $", "\\1", .),
x %>% html_nodes("span.runtime") %>% html_text() %>% gsub("\n", "", .) %>%
gsub(" +", " ", .) %>% gsub("^ (.*) $", "\\1", .) %>% ifelse(length(.)==0, NA, .),
x %>% html_nodes("div.ipl-rating-widget") %>%
html_nodes("div.ipl-rating-star.small") %>%
html_text("span.ipl-rating-star__rating") %>%
gsub("\n", "", .) %>% gsub(" +", " ", .) %>% gsub("^ (.*) $", "\\1", .) %>%
as.numeric() %>%(function(.x) {if (length(.x)!=2) {c(NA, NA)} else {.x}})
) %>%
`names<-`(c("title", "genres", "runtime", "imdbrating", "userating"))
}Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note ("étoile jaune").
movies_list <- "https://www.imdb.com/user/ur56341222/ratings" %>%
read_html() %>%
html_nodes("div#ratings-container.lister-list") %>%
html_nodes("div.lister-item.mode-detail") %>%
lapply(., get_movies_info) %>%
do.call("bind_rows", .)ratings en imagesratings_agg <- read_csv("materials/ratings.csv") %>%
group_by(`Date Rated`) %>%
summarise(
avg_rating = mean(`Your Rating`),
n_rating = n()
) %>%
right_join(
tibble(
"Date Rated" = seq(
from = as.Date("2016-01-01"),
to = as.Date(paste0(lubridate::year(Sys.Date()), "-12-31")),
by = 1
)
)
) %>%
mutate(
Year = lubridate::year(`Date Rated`),
Month = lubridate::month(`Date Rated`, label = TRUE, abbr = FALSE),
wDay = lubridate::wday(`Date Rated`, label = TRUE, abbr = FALSE, week_start = 1),
wDay = factor(wDay, levels = rev(levels(wDay))),
Week = lubridate::isoweek(`Date Rated`),
n_rating = ifelse(is.na(n_rating), 0, n_rating)
) %>%
filter(Year == 2017)
ratings en imagesx_labels <- ratings_agg %>% select(Week, Month) %>% distinct() %>% group_by(Month) %>% summarise(Week = min(Week)+2)
ratings en imagesp <- ratings_agg %>%
ggplot(aes(x = Week, y = wDay, fill = avg_rating, label = n_rating)) +
geom_tile(colour = "grey20", size = 0.1) +
geom_text(colour = "white", fontface = "bold", size = 3) +
facet_wrap(~ Year, ncol = 1) +
scale_fill_viridis(name = "Average", limits = c(0, 10), na.value = "grey30") +
scale_y_discrete(expand = c(0, 0)) +
scale_x_continuous(
expand = c(0, 0),
breaks = x_labels[["Week"]],
labels = x_labels[["Month"]]
) +
theme(
axis.ticks = element_blank(),
axis.text.x = element_text(angle = 45, hjust = 1),
axis.title = element_blank(),
panel.grid = element_blank()
)
ratings en imagesratings en imagesp <- read_csv("materials/ratings.csv") %>%
select(`Your Rating`, `IMDb Rating`) %>%
gather(data = ., key = Who, value = Rating) %>%
ggplot(aes(x = round(Rating, digits = 0), fill = Who)) +
geom_density(
aes(x = Rating, y = (..count../sum(..count..))*100),
bw = 1,
alpha = 0.75,
colour = "white"
) +
geom_histogram(
aes(y = ..count../sum(..count..)),
binwidth = 0.5,
colour = "white",
position = position_dodge(preserve = "single")
) +
scale_x_continuous(name = "Rating", expand = c(0, 0), limits = c(0, 10), breaks = c(0, seq_len(10))) +
scale_y_continuous(expand = c(0, 0), labels = percent, limits = c(0, 0.30)) +
scale_fill_viridis(name = NULL, discrete = TRUE) +
labs(x = "Rating", y = "Proportion", title = "Distribution of Ratings") +
theme(legend.position = c(0, 1), legend.justification = c(-0.05, 1.05))
ratings en imagesratings en imagesggplot2 : http://ggplot2.tidyverse.org/index.html
dbplot : https://github.com/edgararuiz/dbplot
Disponible sur GitHub : nodbi
Supporte :
Package sur le CRAN :
https://github.com/mcanouil/PRESENTATION/tree/master/Rdatabase
Des données ?
Du R tous les jours, ou toutes les semaines :